﻿Imports System.Data.SQLite
Public Class ESQLiteQueryException
    Inherits Exception
    Public Sub New()
        MyBase.New()
    End Sub
    Public Sub New(ByVal Msg As String)
        MyBase.New(Msg)
    End Sub
    Public Sub New(ByVal Msg As String, ByVal ex As Exception)
        MyBase.New(Msg, ex)
    End Sub
End Class
Public Class TSQLiteQuery
    Implements System.IDisposable

    Private _Conn As TSQLiteConnection
    Private _Cmd As New SQLiteCommand
    Private _SQL As New System.Text.StringBuilder
    Private _Eof As Boolean
    Private _da As SQLiteDataAdapter
    Private _ds As DataSet
    Private _Index As Integer

    Public Sub New()

    End Sub

    Public ReadOnly Property Command() As SQLiteCommand
        Get
            Return _Cmd
        End Get
    End Property

    Public Sub DeleteVariable(ByVal AVariableName As String)
        Try
            _Cmd.Parameters.RemoveAt(AVariableName)
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub DeleteVariable(ByVal AIndex As Integer)
        Try
            _Cmd.Parameters.RemoveAt(AIndex)
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub
    Public Sub DeleteVariables()
        Try
            _Cmd.Parameters.Clear()
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub DeclareVariable(ByVal AVariableName As String)
        Try
            Dim param As New System.Data.SQLite.SQLiteParameter(AVariableName, Nothing)
            _Cmd.Parameters.Add(param)
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub SetVariable(ByVal AVariableName As String, ByVal AValue As Object)
        Try
            _Cmd.Parameters(AVariableName).Value = AValue
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub SetVariable(ByVal AIndex As Integer, ByVal AValue As Object)
        Try
            _Cmd.Parameters(AIndex).Value = AValue
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub DeclareAndSet(ByVal AVariableName As String, ByVal AValue As Object)
        Try
            _Cmd.Parameters.AddWithValue(AVariableName, AValue)
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public ReadOnly Property SQL() As System.Text.StringBuilder
        Get
            Return _SQL
        End Get
    End Property

    Public Sub Close()
        Try
            If Not _ds Is Nothing Then
                _ds.Dispose()
            End If
            If Not _da Is Nothing Then
                _da.Dispose()
            End If
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public Sub Clear()
        Try
            _SQL.Length = 0
            Me.DeleteVariables()
            _Cmd.CommandText = String.Empty
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public ReadOnly Property DataSet() As DataSet
        Get
            Return _ds
        End Get
    End Property

    Public Property Connection() As TSQLiteConnection
        Get
            Return _Conn
        End Get
        Set(ByVal value As TSQLiteConnection)
            _Conn = value
            _Cmd.Connection = value.Connection
        End Set
    End Property

    Public Sub Execute()
        Try
            If Left(SQL.ToString.Trim.ToUpper, 6) = "SELECT" Then
                ExecuteSelect()
            Else
                ExecSQL()
            End If
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Private Sub ExecSQL()
        Try
            _Cmd.CommandText = _SQL.ToString
            _Cmd.ExecuteNonQuery()
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Private Sub ExecuteSelect()
        Try
            _Cmd.CommandText = Me.SQL.ToString
            _da = New SQLiteDataAdapter
            _da.SelectCommand = _Cmd
            _ds = New DataSet
            _da.Fill(_ds)
            _Index = 0
        Catch ex As Exception
            Throw New ESQLiteQueryException(ex.Message, ex)
        End Try
    End Sub

    Public ReadOnly Property Eof() As Boolean
        Get
            Return _Index >= _ds.Tables(0).Rows.Count
        End Get
    End Property

    Public Sub MoveNext()
        _Index += 1
    End Sub

    Public ReadOnly Property FieldAsDecimal(ByVal AFieldName As String) As Decimal
        Get
            Return CDec(_ds.Tables(0).Rows(_Index).Item(AFieldName).ToString)
        End Get
    End Property

    Public ReadOnly Property FieldAsDecimal(ByVal AFieldIndex As Integer) As Decimal
        Get
            Return CDec(_ds.Tables(0).Rows(_Index).Item(AFieldIndex).ToString)
        End Get
    End Property

    Public ReadOnly Property FieldAsString(ByVal AFieldName As String) As String
        Get
            Return _ds.Tables(0).Rows(_Index).Item(AFieldName).ToString
        End Get
    End Property
    Public ReadOnly Property FieldAsString(ByVal AFieldIndex As Integer) As String
        Get
            Return _ds.Tables(0).Rows(_Index).Item(AFieldIndex).ToString
        End Get
    End Property

    Public ReadOnly Property FieldAsDateTime(ByVal AFieldName As String) As DateTime
        Get
            Try
                Return DateTime.Parse(_ds.Tables(0).Rows(_Index).Item(AFieldName).ToString)
            Catch ex As Exception
                Return DateTime.MinValue
            End Try
        End Get
    End Property
    Public ReadOnly Property FieldAsDateTime(ByVal AFieldIndex As Integer) As DateTime
        Get
            Try
                Return DateTime.Parse(_ds.Tables(0).Rows(_Index).Item(AFieldIndex).ToString)
            Catch ex As Exception
                Return DateTime.MinValue
            End Try
        End Get
    End Property

    Public ReadOnly Property FieldAsInteger(ByVal AFieldName As String) As Integer
        Get
            Try
                Return CInt(_ds.Tables(0).Rows(_Index).Item(AFieldName).ToString)
            Catch ex As Exception
                Return Nothing
            End Try
        End Get
    End Property
    Public ReadOnly Property FieldAsInteger(ByVal AFieldIndex As Integer) As Integer
        Get
            Try
                Return CInt(_ds.Tables(0).Rows(_Index).Item(AFieldIndex).ToString)
            Catch ex As Exception
                Return Nothing
            End Try
        End Get
    End Property

    Private disposedValue As Boolean = False        ' 重複する呼び出しを検出するには

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                ' TODO: 他の状態を解放します (マネージ オブジェクト)。
            End If
            If Not _ds Is Nothing Then
                _ds.Dispose()
            End If
            If Not _da Is Nothing Then
                _da.Dispose()
            End If
            _Cmd.Dispose()
            ' TODO: ユーザー独自の状態を解放します (アンマネージ オブジェクト)。
            ' TODO: 大きなフィールドを null に設定します。
        End If
        Me.disposedValue = True
    End Sub

#Region " IDisposable Support "
    ' このコードは、破棄可能なパターンを正しく実装できるように Visual Basic によって追加されました。
    Public Sub Dispose() Implements IDisposable.Dispose
        ' このコードを変更しないでください。クリーンアップ コードを上の Dispose(ByVal disposing As Boolean) に記述します。
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class
